﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

namespace HuaCheng.DataAccess
{
   /// <summary>
   /// 对并联表的数据库操作
   /// </summary>
    public class MutiChlHelper
    {
        /// <summary>
        /// 查询并联
        /// </summary>
        /// <param name="unitId"></param>
        /// <returns></returns>
        //public static List<MutiChl> GetChlMap(int unitId)
        //{
        //    List<MutiChl> ret = new List<MutiChl>();
        //    try
        //    {
        //        string sql = "select unit_id,chl_id,parallel_id,insert_time from muti_chl where dev_uid=@id";
        //        DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sql);
        //        using (DbDataReader reader = DBAccessManager.SqlDBAccess.ExecuteReader(cmd))
        //        {
        //            if (reader.HasRows)
        //            {
        //                while (reader.Read())
        //                {
        //                    MutiChl cm = new MutiChl()
        //                    {
        //                        Mid = Convert.ToInt32(reader["unit_id"]),
        //                        ChlId = Convert.ToInt32(reader["chl_id"]),
        //                        ParallelId = Convert.ToInt32(reader["parallel_id"]),
        //                        InsertTime = Convert.ToDateTime(reader["insert_time"]),
        //                    };
        //                    ret.Add(cm);
        //                }
        //            }
        //        }
        //    }
        //    catch (Exception ex) { Console.WriteLine(ex.Message + ex.StackTrace); }
        //    return ret;
        //}
        /// <summary>
        /// 根据实际通道号获取对应的并联通道的所有通道号
        /// </summary>
        /// <param name="realId"></param>
        /// <returns></returns>
        public static List<int> GetChlById(int realId,string devIp,int gid,int mid)
        {
            List<int> ret = new List<int>();
            try
            {
                string sql = "select chl_id from muti_chl where parallel_id = @realId and dev_ip = @devIp and cabinet_id=@gid and mid_id=@mid";
                DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sql);
                cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@realId", realId));
                cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@devIp", devIp));
                cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@gid", gid));
                cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@mid", mid));
                using (DbDataReader reader = DBAccessManager.SqlDBAccess.ExecuteReader(cmd))
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            int chlId = Convert.ToInt32(reader["chl_id"]);
                            ret.Add(chlId);
                        }
                    }
                }
            }
            catch (Exception ex) { Console.WriteLine(ex.Message + ex.StackTrace); }
            return ret;
        }
        /// <summary>
        /// 根据并联组中任意一个通道号获取该组所有的并联通道号
        /// </summary>
        /// <param name="realId"></param>
        /// <param name="devIp"></param>
        /// <returns></returns>
        public static List<int> GetAllChlByOneId(int realId, string devIp)
        {
            List<int> ret = new List<int>();
            try
            {
                string sql = "select real_chl_id from muti_chl as b right join bts_chl_map as a on " +
                    "a.dev_ip = b.dev_ip and a.cabinet_id=b.cabinet_id and a.mid_id = b.mid_id and a.chl_map_id = b.chl_id where " +
                    "parallel_id = (select parallel_id from  muti_chl as b left join bts_chl_map as a " +
                    "on a.dev_ip = b.dev_ip and a.cabinet_id = b.cabinet_id and a.mid_id=b.mid_id and a.chl_map_id = b.chl_id where " +
                    "real_chl_id =@realid)";
                DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sql);
                cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@realid", realId));
                using (DbDataReader reader = DBAccessManager.SqlDBAccess.ExecuteReader(cmd))
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            int chlId = Convert.ToInt32(reader["real_chl_id"]);
                            ret.Add(chlId);
                        }
                    }
                }
            }
            catch (Exception ex) { Console.WriteLine(ex.Message + ex.StackTrace); }
            return ret;
        }
        /// <summary>
        /// 根据柜号通道号获取并联组号
        /// </summary>
        /// <param name="gid"></param>
        /// <param name="cid"></param>
        /// <returns></returns>
        public static int GetParaIdByGidCid(string devIp,int gid,int mid,int cid)
        {
            int paraId = -1;
            string sql = "select parallel_id from muti_chl where cabinet_id = @gid and mid_id=@mid and chl_id = @cid and dev_ip = @devIp";
            DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sql);
            cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@devIp", devIp));
            cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@gid", gid));
            cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@mid", mid));
            cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@cid", cid));
            try
            {
                using (DbDataReader reader = DBAccessManager.SqlDBAccess.ExecuteReader(cmd))
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            paraId = Convert.ToInt32(reader[0]);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message + ex.StackTrace);
            }
            return paraId;
        }
        /// <summary>
        /// 插入并联
        /// </summary>
        /// <param name="muChl"></param>
        public static void InsertParallel(List<MutiChl> muChl)
        {
            foreach (MutiChl chl in muChl)
            {
                try
                {
                    string sqlStr = "insert into muti_chl (dev_ip,cabinet_id,mid_id,chl_id,parallel_id,insert_time) values(@devip,@gid,@mid,@chlid,@paraid,@intime)";
                    using (DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sqlStr))
                    {
                        cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@devip", chl.DevIp));
                        cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@gid", chl.Gid));
                        cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@mid", chl.Mid));
                        cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@chlid", chl.ChlId));
                        cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@paraid", chl.ParallelId));
                        cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@intime", chl.InsertTime));
                        DBAccessManager.SqlDBAccess.ExecuteNonQuery(cmd);
                    }

                }
                catch (Exception ex) { Console.WriteLine(ex.Message + ex.StackTrace); }
            
            }

        }
        /// <summary>
        /// 解除并联
        /// </summary>
        /// <param name="ip"></param>
        /// <param name="parallelId"></param>
        public static void DeleteParallel(string ip,int gid,int mid,int parallelId)
        {
            try
            {
                string sqlStr = "delete from muti_chl where dev_ip=@ip and cabinet_id=@gid and mid_id=@mid and parallel_id=@parallelId";
                using (DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sqlStr))
                {
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@ip", ip));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@gid", gid));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@mid", mid));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@parallelId", parallelId));
                    DBAccessManager.SqlDBAccess.ExecuteNonQuery(cmd);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message + ex.StackTrace);
            }  
        }
    }
}
